<?PHP

ini_set('include_path',ini_get('include_path').':/home/mermexi/local/lib/PEAR:'); 

// DB init
require_once('DB.php');
$db = DB::connect('mysql://mermexi_hangman:hangman@localhost/mermexi_hangman');
if (PEAR::isError($db)) { die ($db->getMessage()); }
$db->setFetchMode(DB_FETCHMODE_ASSOC);

$puzzles_csv_remote_filename = 'http://spreadsheets.google.com/pub?key=p9utksVJsM-X79v2cZXV5Og&output=csv&gid=0';
$categories_csv_remote_filename = 'http://spreadsheets.google.com/pub?key=p9utksVJsM-U4grhHAM-7Wg&output=csv&gid=0';

/*

// this works if you have FILE access to mysql.

$csv_data = file_get_contents($csv_remote_filename);

$csv_file_name = tempnam('/tmp', 'hangman_');

$of = fopen($csv_file_name, 'w+');
fwrite($of, $csv_data);
fclose($of);

chmod($csv_file_name, 0777);

$sql = 'LOAD DATA INFILE "%s" INTO TABLE puzzles FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY \'"\' LINES TERMINATED BY "\n"';

var_dump($db->query(sprintf($sql, $csv_file_name)));

unlink($csv_file_name);
*/

// this is better if you don't have FILE access to mysql

$puzzle_sql = 'INSERT INTO puzzles VALUES (%d, "%s", "%s", %d) ON DUPLICATE KEY UPDATE puzzle_id=%d';
$cat_sql = 'INSERT INTO categories VALUES (%d, "%s") ON DUPLICATE KEY UPDATE cat_id=%d';

$if = fopen($puzzles_csv_remote_filename, 'r');
echo '<pre>' . "\r\n";


while ($csv_row_data = fgetcsv($if, 999)) {

 echo 'Attempting to insert puzzle ' . $csv_row_data[0] . "\t";

 $result = $db->query(sprintf($puzzle_sql, $csv_row_data[0], $csv_row_data[1], $csv_row_data[2], $csv_row_data[3], $csv_row_data[0]));

 if (PEAR::isError($result)) {
  echo 'ERROR: ' . $result->getMessage() . "\r\n";
 } else {
  echo 'OK' . "\r\n";
 }

}

echo "\r\n\r\n";


fclose($if);
$if = fopen($categories_csv_remote_filename, 'r');

while ($csv_row_data = fgetcsv($if, 999)) {

 echo 'Attempting to insert category ' . $csv_row_data[0] . "\t";

 $result = $db->query(sprintf($cat_sql, $csv_row_data[0], $csv_row_data[1], $csv_row_data[0]));

 if (PEAR::isError($result)) {
  echo 'ERROR: ' . $result->getMessage() . "\r\n";
 } else {
  echo 'OK' . "\r\n";
 }

}

echo '</pre>' . "\r\n";

?>
